package com.linc.facerecognitionsi.database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.linc.facerecognitionsi.metadata.FaceData;

import java.util.ArrayList;

/**
 * Created by linc on 15-5-27.
 * main table
 * CREATE TABLE knowledge_base_result(_id INTEGER PRIMARY KEY,
 * title VARCHAR,type VARCHAR, image VARCHAR,preview_content
 * VARCHAR,table_item1 VARCHAR,table_item2 VARCHAR,
 * table_item3 VARCHAR,article_url VARCHAR);
 */
public class FaceTemplateSQLiteHelper extends SQLiteOpenHelper {
    private static final String TAG = "FaceSQLiteHelper";
    public static final String DB_NAME = "face";
    public static final String RESULT_TABLE ="face_template";
    public static final int DB_VERSION = 1;
    private SQLiteDatabase mSQLiteDatabase;

    public FaceTemplateSQLiteHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    public boolean updateKnowledgeBaseWithImage(String title,String previewContent,int id) {
        boolean result = false;
        mSQLiteDatabase = getReadableDatabase();
        String sql = "UPDATE ["+RESULT_TABLE+"] SET title = ? , preview_content = ? " +
                ", type = 'image' WHERE _id = ?";
        try {
            mSQLiteDatabase.execSQL(sql, new Object[]{title, previewContent, id});
            result = true;
        } catch (Exception ex) {
            Log.e(TAG,"updateKnowledgeBaseWithImage failed: "+ex.getMessage());
        } finally {
            mSQLiteDatabase.close();
        }
        return result;
    }

    public boolean updateKnowledgeBaseWithTable(String title,String item1,String item2,
                                             String item3,int id) {
        boolean result = false;
        mSQLiteDatabase = getReadableDatabase();
        String sql = "UPDATE ["+RESULT_TABLE+"] SET title = ? , table_item1 = ? , table_item2 = ?" +
                ",table_item3 = ? , type = 'table' WHERE _id = ?";
        try {
            mSQLiteDatabase.execSQL(sql,new Object[]{title,item1,item2,item3,id});
            result = true;
        } catch (Exception ex) {
            Log.e(TAG,"updateKnowledgeBaseWithTable failed: "+ex.getMessage());
        } finally {
            mSQLiteDatabase.close();
        }
        return result;
    }

    public ArrayList<FaceData> query() {
        Cursor cursor = null;
        ArrayList<FaceData> resultSets = new ArrayList<>();
        try {
            String sql = "SELECT * FROM ["+RESULT_TABLE+"] ";
            Log.d(TAG,"sql: "+sql);
            mSQLiteDatabase = getReadableDatabase();
            cursor = mSQLiteDatabase.rawQuery(sql,null);
            while (cursor.moveToNext()) {
                FaceData resultSet = new FaceData();
                resultSet.name = cursor.getString(1);
                resultSet.idCardNumber = cursor.getString(2);
                resultSet.featureCode = cursor.getString(3);
                resultSet.createTime = cursor.getString(4);
                resultSet.imagePathName = cursor.getString(5);
                resultSets.add(resultSet);
                Log.d(TAG,"name: "+resultSet.name);
            }
            mSQLiteDatabase.close();
        } catch (Exception ex) {
            Log.e(TAG,"failed: "+ex.getMessage());
        } finally {
            closeCursor(cursor);
        }
        Log.d(TAG,"size: "+resultSets.size());
        return resultSets;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    protected void closeCursor(Cursor cursor) {
        if (cursor != null) {
            cursor.close();
        }
    }
}
